﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Anke.SHManage.Utility
{
    /// <summary>
    /// 使用com组件 操作Excel
    /// </summary>
    public class ComExcelHelper
    {
        //private Excel.Application appExcel = null;
        //private Excel.Workbook workbook = null;
        //private Excel.Worksheet sheet = null;
        //private DateTime dtBefore;
        //private DateTime dtAfter;
        //private string filePath;

        //public string FilePath
        //{
        //    get { return filePath; }
        //    set { filePath = value; }
        //}
        //private string timestamp;
        ///// <summary>
        ///// 以时间字符串作为保存文件的名称
        ///// </summary>
        //public string Timestamp
        //{
        //    get { return timestamp; }
        //    set { timestamp = value; }
        //}
        //private object mValue = System.Reflection.Missing.Value;
        ///// <summary>
        /////是否打开Excel界面 
        ///// </summary>
        //public bool Visible
        //{
        //    set
        //    {
        //        appExcel.Visible = value;
        //    }
        //}
        //public ComExcelHelper()
        //{
        //    this.dtBefore = DateTime.Now;
        //    appExcel = new Excel.Application();
        //    this.dtAfter = DateTime.Now;
        //    this.timestamp = DateTime.Now.ToShortDateString().Replace("-", "") + DateTime.Now.ToShortDateString().Replace("-", "") + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString();
        //}
        //public ComExcelHelper(string strFilePath)
        //{
        //    try
        //    {
        //        this.dtBefore = DateTime.Now;
        //        appExcel = new Excel.Application();
        //        this.dtAfter = DateTime.Now;
        //        this.workbook = (Excel.Workbook)appExcel.Workbooks.Open(strFilePath, mValue, false, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue);
        //        this.timestamp = DateTime.Now.ToShortDateString().Replace("-", "") + DateTime.Now.ToShortDateString().Replace("-", "") + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString();
        //    }
        //    catch (Exception ex)
        //    { LogUtility.Error("ComExcelHelper.ComExcelHelper", ex.Message); }
        //}
        //public void Dispose()
        //{
        //    try
        //    {
        //        System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
        //        sheet = null;
        //        workbook.Close(false, mValue, mValue);
        //        System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
        //        workbook = null;
        //        appExcel.Quit();
        //        System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
        //        appExcel = null;
        //        GC.Collect();
        //        GC.WaitForPendingFinalizers();
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    finally
        //    {
        //        foreach (System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel"))
        //        {
        //            if (pro.StartTime > this.dtBefore && pro.StartTime < this.dtAfter)
        //            {
        //                pro.Kill();
        //            }
        //        }
        //    }
        //    System.GC.SuppressFinalize(this);
        //}
        ///// <summary>
        ///// 加载Excel
        ///// </summary>
        //public void Load()
        //{
        //    if (workbook == null && this.filePath != null)
        //    {
        //        workbook = appExcel.Workbooks.Open(this.filePath, mValue, false, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue);
        //    }
        //}
        ///// <summary>
        ///// 加载Excel
        ///// </summary>
        //public void Load(string strFilePath)
        //{
        //    if (workbook == null)
        //    {
        //        workbook = appExcel.Workbooks.Open(strFilePath, mValue, false, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue);
        //    }
        //}
        ///// <summary>
        ///// 新建工作表
        ///// </summary>
        ///// <param name="sheetName"></param>
        //public void NewWorkSheet(string sheetName)
        //{
        //    sheet = workbook.Sheets.Add(workbook.Sheets[1], mValue, mValue, mValue);
        //    sheet.Name = sheetName;
        //}
        ///// <summary>
        ///// 在指定的单元格插入指定的值
        ///// </summary>
        ///// <param name="strCell">单元格 如"A4"</param>
        ///// <param name="objValue">文本 数字等值</param>
        //public void WriteCell(string strCell, object objValue)
        //{
        //    sheet.get_Range(strCell, mValue).Value2 = objValue;
        //}
        ///// <summary>
        ///// 在指定Range中插入指定的值
        ///// </summary>
        ///// <param name="strStartCell">Range的开始单元格</param>
        ///// <param name="strEndCell">Range的结束单元格</param>
        ///// <param name="objValue">文本、数字等值</param>
        //public void WriteRange(string strStartCell, string strEndCell, object objValue)
        //{
        //    sheet.get_Range(strStartCell, strEndCell).Value2 = objValue;
        //}
        ///**/
        ///// <summary>
        ///// 合并单元格，并在合并后的单元格中插入指定的值
        ///// </summary>
        ///// <param name="strStartCell"></param>
        ///// <param name="strEndCell"></param>
        ///// <param name="objValue"></param>
        //public void WriteAfterMerge(string strStartCell, string strEndCell, object objValue)
        //{
        //    sheet.get_Range(strStartCell, strEndCell).Merge(mValue);
        //    sheet.get_Range(strStartCell, mValue).Value2 = objValue;
        //}

        ///**/
        ///// <summary>
        ///// 在连续单元格中插入一个DataTable中的值
        ///// </summary>
        ///// <param name="strStartCell">开始的单元格</param>
        ///// <param name="dtData">存储数据的DataTable</param>
        //public void WriteTable(string strStartCell, System.Data.DataTable dtData)
        //{
        //    object[,] arrData = new object[dtData.Rows.Count, dtData.Columns.Count];

        //    for (int i = 0; i < dtData.Rows.Count; i++)
        //        for (int j = 0; j < dtData.Columns.Count; j++)
        //            arrData[i, j] = dtData.Rows[i][j];

        //    sheet.get_Range(strStartCell, this.GetEndCell(strStartCell, dtData.Rows.Count - 1, dtData.Columns.Count - 1)).Value2 = arrData;

        //    arrData = null;
        //}

        ///**/
        ///// <summary>
        ///// 在连续单元格中插入一个DataTable并作超级链接
        ///// </summary>
        ///// <param name="strStartCell">起始单元格标识符</param>
        ///// <param name="dtData">存储数据的DataTable</param>
        ///// <param name="strLinkField">链接的地址字段</param>
        ///// <param name="strTextField">链接的文本字段</param>
        //public void WriteTableAndLink(string strStartCell, System.Data.DataTable dtData, string strLinkField, string strTextField)
        //{
        //    object[,] arrData = new object[dtData.Rows.Count, dtData.Columns.Count - 1];

        //    for (int i = 0; i < dtData.Rows.Count; i++)
        //    {
        //        for (int j = 0; j < dtData.Columns.Count; j++)
        //        {
        //            if (j > dtData.Columns.IndexOf(strLinkField))
        //                arrData[i, j - 1] = dtData.Rows[i][j];
        //            else if (j < dtData.Columns.IndexOf(strLinkField))
        //                arrData[i, j] = dtData.Rows[i][j];
        //        }
        //    }

        //    sheet.get_Range(strStartCell, this.GetEndCell(strStartCell, dtData.Rows.Count - 1, dtData.Columns.Count - 2)).Value2 = arrData;

        //    for (int i = 0; i < dtData.Rows.Count; i++)
        //        this.AddHyperLink(this.NtoL(this.LtoN(this.GetCellLetter(strStartCell)) + dtData.Columns.IndexOf(strTextField)) + System.Convert.ToString(this.GetCellNumber(strStartCell) + i), dtData.Rows[i][strLinkField].ToString() + ".htm", "点击查看详细", dtData.Rows[i][strTextField].ToString());

        //    arrData = null;
        //}

        ///**/
        ///// <summary>
        ///// 为单元格设置公式
        ///// </summary>
        ///// <param name="strCell">单元格标识符</param>
        ///// <param name="strFormula">公式</param>
        //public void SetFormula(string strCell, string strFormula)
        //{
        //    sheet.get_Range(strCell, mValue).Formula = strFormula;
        //}
        ///**/
        ///// <summary>
        ///// 设置单元格或连续区域的字体为黑体
        ///// </summary>
        ///// <param name="strCell">单元格标识符</param>
        //public void SetBold(string strCell)
        //{
        //    sheet.get_Range(strCell, mValue).Font.Bold = true;
        //}

        ///**/
        ///// <summary>
        ///// 设置连续区域的字体为黑体
        ///// </summary>
        ///// <param name="strStartCell">开始单元格标识符</param>
        ///// <param name="strEndCell">结束单元格标识符</param>
        //public void SetBold(string strStartCell, string strEndCell)
        //{
        //    sheet.get_Range(strStartCell, strEndCell).Font.Bold = true;
        //}

        ///**/
        ///// <summary>
        ///// 设置单元格或连续区域的字体颜色
        ///// </summary>
        ///// <param name="strCell">单元格标识符</param>
        ///// <param name="clrColor">颜色</param>
        //public void SetColor(string strCell, System.Drawing.Color clrColor)
        //{
        //    sheet.get_Range(strCell, mValue).Font.Color = System.Drawing.ColorTranslator.ToOle(clrColor);
        //}

        ///**/
        ///// <summary>
        ///// 设置连续区域的字体颜色
        ///// </summary>
        ///// <param name="strStartCell">开始单元格标识符</param>
        ///// <param name="strEndCell">结束单元格标识符</param>
        ///// <param name="clrColor">颜色</param>
        //public void SetColor(string strStartCell, string strEndCell, System.Drawing.Color clrColor)
        //{
        //    sheet.get_Range(strStartCell, strEndCell).Font.Color = System.Drawing.ColorTranslator.ToOle(clrColor);
        //}

        ///**/
        ///// <summary>
        ///// 设置单元格或连续区域的边框：上下左右都为黑色连续边框
        ///// </summary>
        ///// <param name="strCell">单元格标识符</param>
        //public void SetBorderAll(string strCell)
        //{
        //    sheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
        //    sheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;

        //    sheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
        //    sheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;

        //    sheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
        //    sheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;

        //    sheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
        //    sheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;

        //    sheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
        //    sheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;

        //    sheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
        //    sheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;


        //}

        ///**/
        ///// <summary>
        ///// 设置连续区域的边框：上下左右都为黑色连续边框
        ///// </summary>
        ///// <param name="strStartCell">开始单元格标识符</param>
        ///// <param name="strEndCell">结束单元格标识符</param>
        //public void SetBorderAll(string strStartCell, string strEndCell)
        //{
        //    sheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
        //    sheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;

        //    sheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
        //    sheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;

        //    sheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
        //    sheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;

        //    sheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
        //    sheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;

        //    sheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
        //    sheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;

        //    sheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
        //    sheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
        //}

        ///**/
        ///// <summary>
        ///// 设置单元格或连续区域水平居左
        ///// </summary>
        ///// <param name="strCell">单元格标识符</param>
        //public void SetHAlignLeft(string strCell)
        //{
        //    sheet.get_Range(strCell, mValue).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
        //}

        ///**/
        ///// <summary>
        ///// 设置连续区域水平居左
        ///// </summary>
        ///// <param name="strStartCell">开始单元格标识符</param>
        ///// <param name="strEndCell">结束单元格标识符</param>
        //public void SetHAlignLeft(string strStartCell, string strEndCell)
        //{
        //    sheet.get_Range(strStartCell, strEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
        //}

        ///**/
        ///// <summary>
        ///// 设置单元格或连续区域水平居左
        ///// </summary>
        ///// <param name="strCell">单元格标识符</param>
        //public void SetHAlignCenter(string strCell)
        //{
        //    sheet.get_Range(strCell, mValue).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
        //}

        ///**/
        ///// <summary>
        ///// 设置连续区域水平居中
        ///// </summary>
        ///// <param name="strStartCell">开始单元格标识符</param>
        ///// <param name="strEndCell">结束单元格标识符</param>
        //public void SetHAlignCenter(string strStartCell, string strEndCell)
        //{
        //    sheet.get_Range(strStartCell, strEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
        //}

        ///**/
        ///// <summary>
        ///// 设置单元格或连续区域水平居右
        ///// </summary>
        ///// <param name="strCell">单元格标识符</param>
        //public void SetHAlignRight(string strCell)
        //{
        //    sheet.get_Range(strCell, mValue).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
        //}

        ///**/
        ///// <summary>
        ///// 设置连续区域水平居右
        ///// </summary>
        ///// <param name="strStartCell">开始单元格标识符</param>
        ///// <param name="strEndCell">结束单元格标识符</param>
        //public void SetHAlignRight(string strStartCell, string strEndCell)
        //{
        //    sheet.get_Range(strStartCell, strEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
        //}

        ///**/
        ///// <summary>
        ///// 设置单元格或连续区域的显示格式
        ///// </summary>
        ///// <param name="strCell">单元格标识符</param>
        ///// <param name="strNF">如"#,##0.00"的显示格式</param>
        //public void SetNumberFormat(string strCell, string strNF)
        //{
        //    sheet.get_Range(strCell, mValue).NumberFormat = strNF;
        //}

        ///**/
        ///// <summary>
        ///// 设置连续区域的显示格式
        ///// </summary>
        ///// <param name="strStartCell">开始单元格标识符</param>
        ///// <param name="strEndCell">结束单元格标识符</param>
        ///// <param name="strNF">如"#,##0.00"的显示格式</param>
        //public void SetNumberFormat(string strStartCell, string strEndCell, string strNF)
        //{
        //    sheet.get_Range(strStartCell, strEndCell).NumberFormat = strNF;
        //}

        ///**/
        ///// <summary>
        ///// 设置单元格或连续区域的字体大小
        ///// </summary>
        ///// <param name="strCell">单元格或连续区域标识符</param>
        ///// <param name="intFontSize"></param>
        //public void SetFontSize(string strCell, int intFontSize)
        //{
        //    sheet.get_Range(strCell, mValue).Font.Size = intFontSize.ToString();
        //}

        ///**/
        ///// <summary>
        ///// 设置连续区域的字体大小
        ///// </summary>
        ///// <param name="strStartCell">开始单元格标识符</param>
        ///// <param name="strEndCell">结束单元格标识符</param>
        ///// <param name="intFontSize">字体大小</param>
        //public void SetFontSize(string strStartCell, string strEndCell, int intFontSize)
        //{
        //    sheet.get_Range(strStartCell, strEndCell).Font.Size = intFontSize.ToString();
        //}

        ///**/
        ///// <summary>
        ///// 设置列宽
        ///// </summary>
        ///// <param name="strColID">列标识，如A代表第一列</param>
        ///// <param name="decWidth">宽度</param>
        //public void SetColumnWidth(string strColID, double dblWidth)
        //{
        //    ((Excel.Range)sheet.Columns.GetType().InvokeMember("Item", System.Reflection.BindingFlags.GetProperty, null, sheet.Columns, new object[] { (strColID + ":" + strColID).ToString() })).ColumnWidth = dblWidth;
        //}

        ///**/
        ///// <summary>
        ///// 为单元格添加超级链接
        ///// </summary>
        ///// <param name="strCell">单元格标识符</param>
        ///// <param name="strAddress">链接地址</param>
        ///// <param name="strTip">屏幕提示</param>
        ///// <param name="strText">链接文本</param>
        //public void AddHyperLink(string strCell, string strAddress, string strTip, string strText)
        //{
        //    sheet.Hyperlinks.Add(sheet.get_Range(strCell, mValue), strAddress, mValue, strTip, strText);
        //}

        ///**/
        ///// <summary>
        ///// 已知开始的单元格标识，求intR行、intColumn列后的单元格标识
        ///// </summary>
        ///// <param name="strStartCell">开始单元格标识</param>
        ///// <param name="intR">行数</param>
        ///// <param name="intC">列数</param>
        ///// <returns>单元格标识符结果</returns>
        //public string GetEndCell(string strStartCell, int intR, int intC)
        //{
        //    string endcell = string.Empty;
        //    System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"^(?<vLetter>[A-Z]+)(?<vNumber>\d+)");
        //    if (regex.IsMatch(strStartCell))
        //    {
        //        endcell = this.NtoL(this.LtoN(regex.Match(strStartCell).Result("${vLetter}")) + intC) + System.Convert.ToString((System.Convert.ToInt32(regex.Match(strStartCell).Result("${vNumber}")) + intR));
        //    }
        //    return endcell;

        //}

        ///**/
        ///// <summary>
        ///// 获取单元格标识符中的字母
        ///// </summary>
        ///// <param name="strCell">单元格标识符</param>
        ///// <returns>单元格标识符对应的字母</returns>
        //public string GetCellLetter(string strCell)
        //{
        //    System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"^(?<vLetter>[A-Z]+)(?<vNumber>\d+)");
        //    return regex.Match(strCell).Result("${vLetter}");
        //}

        ///**/
        ///// <summary>
        ///// 获取单元格标识符中的数字
        ///// </summary>
        ///// <param name="strCell">单元格标识符</param>
        //public int GetCellNumber(string strCell)
        //{
        //    System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"^(?<vLetter>[A-Z]+)(?<vNumber>\d+)");
        //    return System.Convert.ToInt32(regex.Match(strCell).Result("${vNumber}"));
        //}


        ///**/
        ///// <summary>
        ///// 另存为xls文件
        ///// </summary>
        ///// <param name="strFilePath">文件路径</param>
        //public void SaveAs(string strFilePath)
        //{
        //    workbook.SaveCopyAs(strFilePath);

        //}

        ///**/
        ///// <summary>
        ///// 另存为xls文件
        ///// </summary>
        ///// <param name="strFilePath">文件路径</param>
        //public void Save()
        //{
        //    workbook.Save();

        //}
        ///// <summary>
        ///// 将Excel中的数据 读入DataTable中
        ///// </summary>
        ///// <param name="filePath">excel文件的路径</param>
        ///// <param name="dtName">datatable的名字</param>
        ///// <param name="headName">表头，以逗号分隔</param>
        ///// <returns></returns>
        //public DataTable ReadExcel2DataTable(string filePath, string dtName, string headName)
        //{
        //    workbook = this.appExcel.Workbooks.Open(filePath, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue);
        //    sheet = workbook.Worksheets.get_Item(1);
        //    DataTable dt = new DataTable(dtName);
        //    string[] heads = !string.IsNullOrEmpty(headName) ? headName.Split(',') : null;
        //    if (heads != null)
        //    {
        //        for (int i = 0; i < heads.Length; i++)
        //        {
        //            DataColumn column = new DataColumn(heads[i]);
        //            //这里没对类型 进行处理
        //            dt.Columns.Add(column);
        //        }
        //        for (int i = 0; i <= sheet.UsedRange.Rows.Count; i++)
        //        {
        //            DataRow dr = dt.NewRow();
        //            for (int j = 0; j < heads.Length; j++)
        //            {
        //                string cellString = NtoL(j + 1);
        //                dr[heads[j]] = sheet.get_Range(cellString + (i + 1)).Value2;

        //            }
        //            dt.Rows.Add(dr);

        //        }
        //    }
        //    else
        //    {
        //        for (int i = 0; i <= sheet.UsedRange.Rows.Count; i++)
        //        {
        //            DataRow dr = dt.NewRow();
        //            for (int j = 0; j <= sheet.UsedRange.Columns.Count; j++)
        //            {
        //                dr[j + 1] = sheet.get_Range(NtoL(j + 1) + (i + 1)).Value2;
        //            }
        //            dt.Rows.Add(dr);
        //        }
        //    }
        //    return dt;
        //}

        ///// <summary>
        ///// 支付宝EXCEL 导入
        ///// 特定格式处理
        ///// 参数：cutHead  去除EXCEL 里头 行数
        /////  参数：cutBottom  去除EXCEL 里尾 行数
        ///// </summary>
        ///// <param name="filePath"></param>
        ///// <param name="dtName"></param>
        ///// <param name="headName"></param>
        ///// <returns></returns>
        //public DataTable ReadAliPayExcel2DataTable(string filePath, string dtName, string headName, int cutHead, int cutBottom)
        //{
        //    // LogUtility.Debug("读取路径是：", filePath);

        //    workbook = this.appExcel.Workbooks.Open(filePath, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue);
        //    sheet = workbook.Worksheets.get_Item(1);
        //    DataTable dt = new DataTable(dtName);
        //    string[] heads = !string.IsNullOrEmpty(headName) ? headName.Split(',') : null;
        //    if (heads != null)
        //    {
        //        for (int i = 0; i < heads.Length; i++)
        //        {
        //            DataColumn column = new DataColumn(heads[i]);
        //            //这里没对类型 进行处理
        //            dt.Columns.Add(column);
        //        }
        //        for (int i = cutHead; i < sheet.UsedRange.Rows.Count - cutBottom; i++) //从第6行开始,不要最后四行
        //        {
        //            DataRow dr = dt.NewRow();
        //            for (int j = 0; j < 7; j++) //只要5个head
        //            {
        //                string cellString = "";
        //                switch (j)
        //                {
        //                    case 0:
        //                        cellString = NtoL(3); //C列 商户订单号
        //                        string dingdStr = Convert.ToString(sheet.get_Range(cellString + (i + 1)).Value2);
        //                        dingdStr = dingdStr.Trim();
        //                        dr[heads[j]] = dingdStr;
        //                        break;
        //                    case 1:
        //                        //cellString = NtoL(2); //E列
        //                        //dr[heads[j]] = sheet.get_Range(cellString + (i + 1)).Value2;
        //                        dr[heads[j]] = DateTime.Now;
        //                        break;
        //                    case 2:
        //                        dr[heads[j]] = 1; //支付类型为1：支付宝，2：微信
        //                        break;
        //                    case 3:
        //                        cellString = NtoL(2); //B列
        //                        // dr[heads[j]] = sheet.get_Range(cellString + (i + 1)).Value2;
        //                        string yewStr = Convert.ToString(sheet.get_Range(cellString + (i + 1)).Value2);
        //                        yewStr = yewStr.Trim();
        //                        dr[heads[j]] = yewStr;
        //                        break;
        //                    case 4:
        //                        cellString = NtoL(7); //G列
        //                        dr[heads[j]] = sheet.get_Range(cellString + (i + 1)).Value2;
        //                        break;
        //                    case 5:
        //                        cellString = NtoL(11); //K列
        //                        dr[heads[j]] = sheet.get_Range(cellString + (i + 1)).Value2;
        //                        break;
        //                    case 6:
        //                        cellString = NtoL(12); //L列
        //                        dr[heads[j]] = sheet.get_Range(cellString + (i + 1)).Value2;
        //                        break;
        //                    default:
        //                        break;
        //                }
        //            }
        //            dt.Rows.Add(dr);

        //        }
        //    }

        //    return dt;
        //}

        ///// <summary>
        ///// 微信EXCEL 导入
        ///// 特定格式处理
        ///// </summary>
        ///// <param name="filePath"></param>
        ///// <param name="dtName"></param>
        ///// <param name="headName"></param>
        ///// <returns></returns>
        //public DataTable ReadWeiXinPayExcel2DataTable(string filePath, string dtName, string headName, int cutHead, int cutBottom)
        //{
        //    DataTable dt = null;
        //    try
        //    {
        //        dt = new DataTable(dtName);
        //        workbook = this.appExcel.Workbooks.Open(filePath, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue);
        //        sheet = workbook.Worksheets.get_Item(1);
        //        string[] heads = !string.IsNullOrEmpty(headName) ? headName.Split(',') : null;
        //        if (heads != null)
        //        {
        //            for (int i = 0; i < heads.Length; i++)
        //            {
        //                DataColumn column = new DataColumn(heads[i]);
        //                //这里没对类型 进行处理
        //                dt.Columns.Add(column);
        //            }
        //            for (int i = cutHead; i < sheet.UsedRange.Rows.Count - cutBottom; i++) //从第6行开始
        //            {
        //                DataRow dr = dt.NewRow();
        //                for (int j = 0; j < 7; j++) //只要7个head
        //                {
        //                    string cellString = "";
        //                    switch (j)
        //                    {
        //                        case 0:
        //                            cellString = NtoL(3); //C列   商户订单号
        //                            string dingdhStr = Convert.ToString(sheet.get_Range(cellString + (i + 1)).Value2);
        //                            dingdhStr = dingdhStr.Trim();
        //                            dr[heads[j]] = dingdhStr.Substring(1, dingdhStr.Length - 1);
        //                            break;
        //                        case 1:

        //                            dr[heads[j]] = DateTime.Now;
        //                            break;
        //                        case 2:
        //                            dr[heads[j]] = 2; //支付类型为1：支付宝，2：微信
        //                            break;
        //                        case 3:
        //                            cellString = NtoL(2); //B列 交易流水号
        //                            string jiaoyStr = Convert.ToString(sheet.get_Range(cellString + (i + 1)).Value2);
        //                            jiaoyStr = jiaoyStr.Trim();
        //                            dr[heads[j]] = jiaoyStr.Substring(1, jiaoyStr.Length - 1);

        //                            break;
        //                        case 4:
        //                            cellString = NtoL(6); //F列 订单金额
        //                            dr[heads[j]] = sheet.get_Range(cellString + (i + 1)).Value2;
        //                            break;
        //                        case 5:
        //                            cellString = NtoL(5); //E列 状态
        //                            dr[heads[j]] = sheet.get_Range(cellString + (i + 1)).Value2;
        //                            break;
        //                        case 6:
        //                            cellString = NtoL(4); //D列 备注
        //                            dr[heads[j]] = sheet.get_Range(cellString + (i + 1)).Value2;
        //                            break;
        //                        default:
        //                            break;
        //                    }
        //                }
        //                dt.Rows.Add(dr);

        //            }
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        Utility.LogUtility.Error(".ReadWeiXinPayExcel2DataTable", ex.Message);
        //        dt = null;
        //    }

        //    return dt;
        //}



        ///**/
        ///// <summary>
        ///// 另存为html文件
        ///// </summary>
        ///// <param name="strFilePath">文件路径</param>
        //public void SaveHtml(string strFilePath)
        //{
        //    workbook.SaveAs(strFilePath, Excel.XlFileFormat.xlHtml, mValue, mValue, mValue, mValue, Excel.XlSaveAsAccessMode.xlNoChange, mValue, mValue, mValue, mValue, mValue);
        //}

        //public void CreateHtmlFile()
        //{

        //}


        ///**/
        ///// <summary>
        ///// 字母转换为数字，Excel列头，如A-1;AA-27
        ///// </summary>
        ///// <param name="strLetter">字母</param>
        ///// <returns>字母对应的数字</returns>
        //private int LtoN(string strLetter)
        //{
        //    int intRtn = 0;

        //    string strLetters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

        //    if (strLetter.Length == 2)
        //        intRtn += (strLetters.IndexOf(strLetter.Substring(0, 1)) + 1) * 26;

        //    intRtn += strLetters.IndexOf(strLetter.Substring(strLetter.Length - 1, 1)) + 1;

        //    return intRtn;

        //}

        ///**/
        ///// <summary>
        ///// 数字转换为字母，Excel列头，如1-A;27-AA
        ///// </summary>
        ///// <param name="intNumber">数字</param>
        ///// <returns>数字对应的字母</returns>
        //private string NtoL(int intNumber)
        //{
        //    if (intNumber > 702)
        //        return String.Empty;

        //    if (intNumber == 702)
        //        return "ZZ";

        //    string strRtn = String.Empty;

        //    string strLetters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

        //    if (intNumber > 26)
        //        strRtn = strLetters.Substring(intNumber / 26 - 1, 1);

        //    strRtn += strLetters.Substring((intNumber % 26) - 1, 1);

        //    return strRtn;
        //}

        /// <summary>
        /// 批量存入数据库
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static string Insert2Sql(DataTable dt)
        {
            string flag = "";
            DataTable dtCopy = dt.Copy();
            DataView dv = dt.DefaultView;
            dv.Sort = "商户订单号";
            dtCopy = dv.ToTable();
            string firstStr = Convert.ToString(dtCopy.Rows[0]["商户订单号"]);

            string ConnectionString = AppConfig.GetConnectionString("MainConnectionString");
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                //先查一下有没有重复的
                SqlCommand comm = conn.CreateCommand();
                comm.CommandTimeout = 20;
                comm.CommandType = CommandType.Text;
                comm.CommandText = "select 商户订单号 from  I_PayDetailEXCEL where 商户订单号>='" + firstStr + "'";
                SqlDataAdapter adapter = new SqlDataAdapter(comm);
                DataSet ds = new DataSet();
                adapter.Fill(ds);

                foreach (DataRow trow in ds.Tables[0].Rows)
                {
                    DataRow[] myrow = dtCopy.Select("商户订单号='" + trow["商户订单号"] + "'");
                    if (myrow.Length > 0)
                    {
                        foreach (DataRow row in myrow)
                        {
                            //删除已存在的
                            dtCopy.Rows.Remove(row);
                        }
                    }
                }

                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction))
                    {
                        bulkCopy.BatchSize = 100000;
                        bulkCopy.BulkCopyTimeout = 3;
                        bulkCopy.DestinationTableName = "I_PayDetailEXCEL"; ;  //批量插入到的表名 
                        try
                        {
                            if (dtCopy.Rows.Count > 0)
                            {
                                foreach (DataColumn col in dtCopy.Columns)
                                {
                                    bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                                }
                                bulkCopy.WriteToServer(dtCopy);

                                transaction.Commit();
                                //    flag = true;
                            }
                        }
                        catch (Exception ex)
                        {
                            //   flag = false;
                            transaction.Rollback();
                            flag = ex.Message;
                            LogUtility.Error(" ComExcelHelper.Insert2Sql", ex.Message);
                        }
                        finally
                        {
                            conn.Close();
                        }
                        return flag;
                    }
                }
            }
        }

        /// <summary>
        /// 批量更新数据(每批次5000) 
        /// </summary>
        /// <param name="table"></param>
        /// <param name="courseId"></param>
        /// <param name="SchemeId"></param>
        /// <returns></returns>
        public bool Update(DataTable table, int courseId, int SchemeId)
        {
            table.TableName = "1";
            DataSet ds = new DataSet();
            bool flag = false;

            string ConnectionString = AppConfig.GetConnectionString("MainConnectionString");
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand comm = conn.CreateCommand();
            comm.CommandTimeout = 20;

            DataTable dtCopy = table.Copy();
            DataView dv = table.DefaultView;
            dv.Sort = "商户订单号";
            dtCopy = dv.ToTable();
            string firstStr = Convert.ToString(dtCopy.Rows[0]["商户订单号"]);

            comm.CommandType = CommandType.Text;
            comm.CommandText = "select 商户订单号 from  I_PayDetailEXCEL where 商户订单号>=" + firstStr + "";
            SqlDataAdapter adapter = new SqlDataAdapter(comm);
            SqlCommandBuilder commandBulider = new SqlCommandBuilder(adapter);
            commandBulider.ConflictOption = ConflictOption.OverwriteChanges;
            try
            {
                conn.Open();
                //设置批量更新的每次处理条数 这就是批量更新
                adapter.UpdateBatchSize = 100000;
                adapter.SelectCommand.Transaction = conn.BeginTransaction();/////////////////开始事务
                adapter.Fill(ds);
                foreach (DataRow trow in ds.Tables[0].Rows)
                {
                    trow.BeginEdit();
                    DataRow[] myrow = table.Select("商户订单号='" + trow["商户订单号"] + "'");
                    if (myrow.Length > 0)
                    {
                        //创建时间,交易类型,交易流水号,订单金额,状态,备注
                        trow["商户订单号"] = myrow[0]["商户订单号"].ToString();
                        trow["创建时间"] = myrow[0]["创建时间"].ToString();
                        trow["交易类型"] = myrow[0]["交易类型"].ToString();
                        trow["订单金额"] = (myrow[0]["订单金额"].ToString());
                        trow["状态"] = Convert.ToString(myrow[0]["状态"]);
                        trow["备注"] = (myrow[0]["备注"].ToString());
                    }
                    trow.EndEdit();
                }

                adapter.Update(ds);
                adapter.SelectCommand.Transaction.Commit();/////提交事务 
                flag = true;
            }
            catch (Exception ex)
            {
                flag = false;
                if (adapter.SelectCommand != null && adapter.SelectCommand.Transaction != null)
                {
                    adapter.SelectCommand.Transaction.Rollback();
                }
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return flag;
        }

        //将文件流读取到datatable 中
        public static DataTable OpenAliCSV(Stream steam, int headCut)
        {
            try
            {
                //Encoding encoding = Common.GetType(filePath); //Encoding.ASCII;//
                DataTable dt = new DataTable();
                //FileStream fs = new FileStream(filePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);
                //StreamReader sr = new StreamReader(fs, Encoding.UTF8);
                StreamReader sr = new StreamReader(steam, Encoding.Default);

                //记录每次读取的一行记录
                string strLine = "";
                //记录每行记录中的各字段内容
                string[] aryLine = null;
                //string[] tableHead = null;
                //标示列数
                //逐行读取CSV中的数据
                int row = 0;
                dt.Columns.Add("商户订单号");
                dt.Columns.Add("创建时间");
                dt.Columns.Add("交易类型");
                dt.Columns.Add("交易流水号");
                dt.Columns.Add("订单金额");
                dt.Columns.Add("状态");
                dt.Columns.Add("备注");

                while ((strLine = sr.ReadLine()) != null)
                {
                    //strLine = Common.ConvertStringUTF8(strLine, encoding);
                    //strLine = Common.ConvertStringUTF8(strLine);
                    if (row >= headCut)
                    {
                        aryLine = strLine.Split(',');
                        if (aryLine.Length >= 7)
                        {
                            if (aryLine[2].Replace("\"", "").Trim() != string.Empty)
                            {
                                DataRow dr = dt.NewRow();
                                for (int j = 0; j < 7; j++)
                                {
                                    switch (j)
                                    {
                                        //第3 列 商户订单号
                                        case 0:
                                            string dingdStr = Convert.ToString(aryLine[2]);
                                            dr[j] = dingdStr.Replace("\"", "").Trim();
                                            break;
                                        //第5列 创建时间
                                        case 1:
                                            string shijStr = Convert.ToString(aryLine[4]);
                                            dr[j] = shijStr.Replace("\"", "").Trim();
                                            break;
                                        //交易类型
                                        case 2:
                                            dr[j] = 1;
                                            break;
                                        //交易流水号
                                        case 3:
                                            string jiaoyStr = Convert.ToString(aryLine[1]);
                                            dr[j] = jiaoyStr.Replace("\"", "").Trim();
                                            break;
                                        //订单金额
                                        case 4:
                                            string jineStr = Convert.ToString(aryLine[6]);
                                            dr[j] = jineStr.Replace("\"", "").Trim();
                                            break;
                                        //状态
                                        case 5:
                                            string zhuangtStr = Convert.ToString(aryLine[10]);
                                            dr[j] = zhuangtStr.Replace("\"", "").Trim();
                                            break;
                                        //备注
                                        case 6:
                                            string beizStr = Convert.ToString(aryLine[11]);
                                            dr[j] = beizStr.Replace("\"", "").Trim();
                                            break;
                                    }
                                }
                                dt.Rows.Add(dr);
                            }
                        }
                    }
                    row++;
                }
                if (aryLine != null && aryLine.Length > 0)
                {
                    dt.DefaultView.Sort = dt.Columns[0] + " " + "asc";
                }
                sr.Close();
                steam.Close();
                sr.Dispose();
                steam.Dispose();
                return dt;
            }
            catch (Exception ex)
            {

                steam.Close();
                steam.Dispose();
                Utility.LogUtility.Error("ComExcelHelper.OpenAliCSV", ex.Message);
                return null;
            }
        }


        public static DataTable OpenWeiXinCSV(Stream steam, int headCut)
        {
            try
            {
                DataTable dt = new DataTable();
                StreamReader sr = new StreamReader(steam, Encoding.Default);

                //记录每次读取的一行记录
                string strLine = "";
                //记录每行记录中的各字段内容
                string[] aryLine = null;
                //  string[] tableHead = null;
                //标示列数
                //逐行读取CSV中的数据
                int row = 0;
                dt.Columns.Add("商户订单号");
                dt.Columns.Add("交易时间");
                dt.Columns.Add("交易类型");
                dt.Columns.Add("平台订单号");
                dt.Columns.Add("实收金额");
                dt.Columns.Add("交易状态");

                while ((strLine = sr.ReadLine()) != null)
                {
                    if (row >= headCut)
                    {
                        aryLine = strLine.Split(',');
                        if (aryLine.Length > 34)
                        {
                            //&& aryLine[11].Replace("\"", "").Replace("`", "").Trim() == "支付成功"
                            if (aryLine[8].Replace("\"", "").Replace("`", "").Trim() != string.Empty)
                            {
                                DataRow dr = dt.NewRow();
                                for (int j = 0; j < 6; j++)
                                {
                                    switch (j)
                                    {
                                        //第3 列 商户订单号
                                        case 0:
                                            string dingdStr = Convert.ToString(aryLine[8]);
                                            string tuikuandanhao = Convert.ToString(aryLine[17]);
                                            dr[j] = tuikuandanhao.Length >= 20 ? tuikuandanhao.Replace("\"", "").Replace("`", "").Trim() : dingdStr.Replace("\"", "").Replace("`", "").Trim();
                                            break;
                                        //第5列 交易时间
                                        case 1:
                                            string shijStr = Convert.ToString(aryLine[0]);
                                            dr[j] = shijStr.Replace("\"", "").Replace("`", "").Trim();
                                            break;
                                        //交易类型
                                        case 2:
                                            string typeStr = Convert.ToString(aryLine[10]);
                                            dr[j] = typeStr.Replace("\"", "").Replace("`", "").Trim();
                                            break;
                                        //平台订单号
                                        case 3:
                                            string jiaoyStr = Convert.ToString(aryLine[6]);
                                            dr[j] = jiaoyStr.Replace("\"", "").Replace("`", "").Trim();
                                            break;
                                        //实收金额
                                        case 4:
                                            string jineStr = Convert.ToString(aryLine[33]);
                                            dr[j] = jineStr.Replace("\"", "").Replace("`", "").Trim();
                                            break;
                                        //交易状态
                                        case 5:
                                            string zhuangtStr = Convert.ToString(aryLine[11]);
                                            dr[j] = zhuangtStr.Replace("\"", "").Replace("`", "").Trim();
                                            break;
                                    }
                                }
                                dt.Rows.Add(dr);
                            }
                        }
                    }
                    row++;
                }
                if (aryLine != null && aryLine.Length > 0)
                {
                    dt.DefaultView.Sort = dt.Columns[0] + " " + "asc";
                }
                sr.Close();
                steam.Close();
                sr.Dispose();
                steam.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                steam.Close();
                steam.Dispose();
                Utility.LogUtility.Error("ComExcelHelper.OpenWeiXinCSV", ex.Message);
                return null;
            }
        }

    }
}
